﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace _4DJackPot
{
    public partial class DbConn
    {
        public DbConn()
        {
            ReadDbType();
        }

        public void ReadDbType()
        {
            try
            {
                if (Global.dbType >= 0 && Global.dbType <= 2) return;

                Ini Ini = new Ini();
                // DATABASE 정의
                string dbDefineString = Ini.DefaultFileReadIni("MACRO", "KINDDB") ?? "";
                if (dbDefineString == "0") Global.dbType = 0;
                else if (dbDefineString == "1") Global.dbType = 1;
                else if (dbDefineString == "2") Global.dbType = 2;

                // MS ACCESS
                Global.dbFileString = Ini.DefaultFileReadIni("MACRO", "DBPATH") ?? "";
                // MS SQL
                Global.dbServerAddrString = Ini.DefaultFileReadIni("MACRO", "SERVERADDR");
                Global.dbNameString = Ini.DefaultFileReadIni("MACRO", "DBNAME");
                Global.dbUidString = Ini.DefaultFileReadIni("MACRO", "UID");
                Global.dbPidString = Ini.DefaultFileReadIni("MACRO", "PID");
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);
            }
        }

        public bool DbConnectTestMSAccess(string dbFileString)
        {
            try
            {
                if (File.Exists(dbFileString))
                {
                    string dbPathString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + dbFileString + ";";
                    if (Global.dBCon != null && Global.dBCon.State != ConnectionState.Closed)
                    {
                        try
                        {
                            Global.dBCon.Close();
                        }
                        catch (Exception) { }
                    }
                    Global.dBCon = new OleDbConnection(dbPathString);
                    try
                    {
                        Global.dBCon.Open();
                        Global.dBCon.Close();
                    }
                    catch (Exception e1)
                    {
                        FileLog FileLog = new FileLog();
                        FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                        MessageBox.Show("MS Access 연결에 실패했습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);

                        return false;
                    }
                }
                else
                {
                    MessageBox.Show("MS Access 파일이 존재하지 않습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return false;
                }
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                return false;
            }

            return true;
        }

        public bool DbConnectTestMSSQL(string serverAddrString, string dbNameString, string uidString, string pidString)
        {
            try
            {
                // MS SQL
                string dbPathString =
                    "Data Source=" + serverAddrString.Trim() + ";Provider=SQLOLEDB;" +
                    "Initial Catalog=" + dbNameString + ";" +
                    "User ID=" + uidString + ";" +
                    "Password=" + pidString + ";";
                if (Global.dBCon != null && Global.dBCon.State != ConnectionState.Closed)
                {
                    try
                    {
                        Global.dBCon.Close(); 
                    }
                    catch (Exception) { }
                }
                Global.dBCon = new OleDbConnection(dbPathString);
                try
                {
                    Global.dBCon.Open();
                    Global.dBCon.Close();
                }
                catch (Exception e1)
                {
                    FileLog FileLog = new FileLog();
                    FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                    MessageBox.Show("MS SQL 연결에 실패했습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);


                    return false;
                }
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                return false;
            }

            return true;
        }

        public bool DbConnect(bool initCheckBool)
        {
            try
            {
                if (Global.dBCon != null && Global.dBCon.State != ConnectionState.Closed && Global.dBCmd != null) return true;
                /* 
                 * dbType : 0 (MS Access)
                 * dbType : 1 (MS Sql)
                 * dbType : 2 (Oracle)
                 */

                // MS Access
                if (Global.dbType == 0)
                {
                    if (File.Exists(Global.dbFileString))
                    {
                        string dbPathString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Global.dbFileString + ";";
                        Global.dBCon = new OleDbConnection(dbPathString);
                        try
                        {
                            Global.dBCon.Open();
                            Global.dBCmd = new OleDbCommand();
                            Global.dBCmd.Connection = Global.dBCon;
                            if (initCheckBool)
                            {
                                Console.WriteLine("DB:: Began Transaction");
                                BeginTran();
                                // DB INIT
                                DbInitMSACCESS sPCDbInitMSACCESS = new DbInitMSACCESS();
                                if (!sPCDbInitMSACCESS.ExecInit())
                                {
                                    RollBack();
                                }
                                else Commit();
                            }
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("MS Access 연결에 실패했습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);

                            return false;
                        }
                    }
                    else
                    {
                        MessageBox.Show("MS Access 파일이 존재하지 않습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return false;
                    }

                    return true;
                }
                // MS SQL
                else if (Global.dbType == 1)
                {
                    // MS SQL
                    string dbPathString =
                        "Data Source=" + Global.dbServerAddrString.Trim() + ";Provider=SQLOLEDB;" +
                        "Initial Catalog=" + Global.dbNameString + ";" +
                        "User ID=" + Global.dbUidString + ";" +
                        "Password=" + Global.dbPidString + ";";
                    if (Global.dBCon != null && Global.dBCon.State != ConnectionState.Closed)
                    {
                        try
                        {
                            Global.dBCon.Close();
                        }
                        catch (Exception) { }
                    }
                    Global.dBCon = new OleDbConnection(dbPathString);
                    try
                    {
                        Global.dBCon.Open();
                        Global.dBCmd = new OleDbCommand();
                        Global.dBCmd.Connection = Global.dBCon;
                        if (initCheckBool)
                        {
                            BeginTran();
                            // DB INIT
                            /*
                            SPCDbInitMSSQL sPCDbInitMSSQL = new SPCDbInitMSSQL();
                            if (!ExecSP(sPCDbInitMSSQL.GetInitString()))
                            {
                                RollBack();
                            }
                            else Commit();
                             * */
                        }
                    }
                    catch (Exception e1)
                    {
                        FileLog FileLog = new FileLog();
                        FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                        MessageBox.Show("MS SQL 연결에 실패했습니다.", "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);

                        return false;
                    }

                    return true;
                }
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                return false;
            }

            return false;
        }

        public void BeginTran()
        {
            try
            {
                Console.WriteLine("Begin Tran()");

                if (!DbConnect(false)) return;

                Global.dBTrans = Global.dBCon.BeginTransaction();
                Global.dBCmd.Transaction = Global.dBTrans;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);
            }
        }

        public void RollBack()
        {
            try
            {
                Global.dBTrans.Rollback();
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);
            }
        }

        public void Commit()
        {
            try
            {
                Global.dBTrans.Commit();
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);
            }
        }

        public DataSet ReadQuery(string readQueryString)
        {
            try
            {
                if (!DbConnect(false)) return null;

                if (!readQueryString.Contains("MAX(DATA_SEQ) + 1"))
                {
                    OleDbTransaction prevTrans = Global.dBTrans;
                    Global.dBTrans = Global.dBCon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                    Global.dBCmd.Transaction = Global.dBTrans;

                    Global.dBCmd.CommandText = readQueryString;
                    Global.dBCmd.ExecuteScalar();
                    Global.dBDataSet = new DataSet();
                    Global.dBAdapter = new OleDbDataAdapter(Global.dBCmd.CommandText, Global.dBCon);
                    Global.dBAdapter.SelectCommand.Transaction = Global.dBTrans;
                    Global.dBAdapter.Fill(Global.dBDataSet);
                    Global.dBAdapter.Dispose();

                    Global.dBTrans.Commit();
                    Global.dBTrans = prevTrans;
                }
                else
                {
                    Global.dBCmd.CommandText = readQueryString;
                    Global.dBCmd.ExecuteScalar();
                    Global.dBDataSet = new DataSet();
                    Global.dBAdapter = new OleDbDataAdapter(Global.dBCmd.CommandText, Global.dBCon);
                    Global.dBAdapter.SelectCommand.Transaction = Global.dBTrans;
                    Global.dBAdapter.Fill(Global.dBDataSet);
                    Global.dBAdapter.Dispose();
                }

                return Global.dBDataSet;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);
                Global.dBTrans.Rollback();

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return null;
        }

        public DataSet ReadQueryNotNolock(string readQueryString)
        {
            try
            {
                if (!DbConnect(false)) return null;

                Global.dBCmd.CommandText = readQueryString;
                Global.dBCmd.ExecuteScalar();
                Global.dBDataSet = new DataSet();
                Global.dBAdapter = new OleDbDataAdapter(Global.dBCmd.CommandText, Global.dBCon);
                Global.dBAdapter.SelectCommand.Transaction = Global.dBTrans;
                Global.dBAdapter.Fill(Global.dBDataSet);
                Global.dBAdapter.Dispose();



                return Global.dBDataSet;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return null;
        }

        public bool UpdateQuery(string updateQueryString)
        {
            try
            {
                if (!DbConnect(false)) return false;

                Global.dBCmd.CommandText = updateQueryString;
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return false;
        }

        public bool UpdateBinaryDataQuery(string updateQueryString, string paramName, byte[] data)
        {
            try
            {
                if (!DbConnect(false)) return false;

                Global.dBCmd.CommandText = updateQueryString;
                OleDbParameter param = Global.dBCmd.Parameters.Add(paramName, OleDbType.Binary);
                param.Value = data;
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.Parameters.Clear();

                return true;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return false;
        }

        public bool InsertQuery(string insertQueryString)
        {
            try
            {
                if (!DbConnect(false)) return false;

                Global.dBCmd.CommandText = insertQueryString;
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message + "\n" + insertQueryString);
                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return false;
        }

        public bool ExecSP(string spQueryString)
        {
            try
            {
                DataSet dataSet = ReadQueryNotNolock(
                    "SELECT COUNT(*) FROM sys.objects WHERE type_desc = 'SQL_STORED_PROCEDURE' AND name = 'SP_INIT_SPCTABLE'");
                string resultString = dataSet.Tables[0].Rows[0][0].ToString();
                int getInt = 0;
                if (Int32.TryParse(resultString, out getInt))
                {
                    if (getInt == 0)
                    {
                        Global.dBCmd.CommandText = spQueryString;
                        int resultInt = Global.dBCmd.ExecuteNonQuery();
                    }
                }
                else return false;

                Global.dBCmd = new OleDbCommand("SP_INIT_SPCTABLE", Global.dBCon);
                Global.dBCmd.CommandType = CommandType.StoredProcedure;

                Global.dBCmd.Transaction = Global.dBTrans;
                Global.dBCmd.ExecuteNonQuery();

                Global.dBCmd = new OleDbCommand();
                Global.dBCmd.Connection = Global.dBCon;
                Global.dBCmd.CommandType = CommandType.Text;

                return true;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return false;
        }

        public bool DeleteQuery(string deleteQueryString)
        {
            try
            {
                if (!DbConnect(false)) return false;

                Global.dBCmd.CommandText = deleteQueryString;
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                FileLog FileLog = new FileLog();
                FileLog.WriteErrorLog(e1.StackTrace + "\n" + e1.Message);

                MessageBox.Show(e1.Message.ToString(), "도움말", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return false;
        }
    }
}
